﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SHCustHelpTools
{
    public partial class Form1 : Form
    {
        private SqlConnection con;
        private SqlDataAdapter dap;
        private DataSet ds;
        private string sqlconstr = "workstation id=NETQ;packet size=4096;user id=sa;data source=172.16.100.123 ;persist security info=True;initial catalog=sanhuadata20131011;password=Aa1";

        AutoSizeFormClass asc = new AutoSizeFormClass();//主form自适应
        AutoSizeFormClass asc1 = new AutoSizeFormClass();//tabPage1自适应
        AutoSizeFormClass asc2 = new AutoSizeFormClass();//tabPage2自适应
        AutoSizeFormClass asc3 = new AutoSizeFormClass();//tabPage3自适应

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            asc.controllInitializeSize(this);
            textBox4.Enabled = false;
        }

        private void Form1_SizeChanged(object sender, EventArgs e)
        {
            asc.controlAutoSize(this);
        }

        #region   数据字典
        //选到“数据字典”页签时触发
        private void tabPage1_Enter(object sender, EventArgs e)
        {
            asc1.controllInitializeSize(this.tabPage1);
            string sqlConStr = sqlconstr;
            con = new SqlConnection(sqlConStr);
            dap = new SqlDataAdapter("", this.con);
            ds = new DataSet();
        }
       
        private void button1_Click(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource != null && ((DataTable)dataGridView1.DataSource).Rows.Count != 0)
            {
                ((DataTable)dataGridView1.DataSource).Clear();
            }
            StringBuilder sql = new StringBuilder();
            sql.Append(" SELECT                                                                                                " + Environment.NewLine);
            sql.Append(" 表名 = CASE WHEN A.COLORDER=1 THEN D.NAME ELSE '' END,                                                " + Environment.NewLine);
            sql.Append(" 表备注 = CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,'') ELSE '' END,                                  " + Environment.NewLine);
            sql.Append(" 序号 = A.COLORDER,                                                                                  " + Environment.NewLine);
            sql.Append(" 列名称 = A.NAME,                                                                                      " + Environment.NewLine);
            sql.Append(" 标识 = CASE WHEN COLUMNPROPERTY(A.ID,A.NAME,'ISIDENTITY')=1 THEN '√ ' ELSE '' END,                   " + Environment.NewLine);
            sql.Append(" 主键 = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN (   " + Environment.NewLine);
            sql.Append(" SELECT NAME FROM SYSINDEXES WHERE INDID IN(                                                           " + Environment.NewLine);
            sql.Append(" SELECT INDID FROM SYSINDEXKEYS WHERE ID=A.ID AND COLID=A.COLID))) THEN '√' ELSE '' END,              " + Environment.NewLine);
            sql.Append(" 类型 = B.NAME,                                                                                        " + Environment.NewLine);
            sql.Append(" 字节 = A.LENGTH,                                                                                      " + Environment.NewLine);
            sql.Append(" 长度 = COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'),                                                       " + Environment.NewLine);
            sql.Append(" 小数位 = ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0),                                               " + Environment.NewLine);
            sql.Append(" 允许空 = CASE WHEN A.ISNULLABLE=1 THEN '√ 'ELSE '' END,                                              " + Environment.NewLine);
            sql.Append(" 默认值 = ISNULL(E.TEXT,''),                                                                           " + Environment.NewLine);
            sql.Append(" 列备注 = ISNULL(G.[VALUE],'')                                                                         " + Environment.NewLine);
            sql.Append(" FROM                                                                                                  " + Environment.NewLine);
            sql.Append(" SYSCOLUMNS A                                                                                          " + Environment.NewLine);
            sql.Append(" LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE                                                       " + Environment.NewLine);
            sql.Append(" INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE='U ' --AND D.NAME<>'DTPROPERTIES'                    " + Environment.NewLine);
            sql.Append(" LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID                                                            " + Environment.NewLine);
            sql.Append(" LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id                         " + Environment.NewLine);
            sql.Append(" LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0                               " + Environment.NewLine);
            sql.Append(" where D.NAME='" + textBox1.Text.Trim() + "'   --查询这个表                                            " + Environment.NewLine);
            sql.Append("     --AND A.name = 'id'    --查询这个字段                                                             " + Environment.NewLine);
            sql.Append(" ORDER BY A.ID,A.COLORDER                                                                              " + Environment.NewLine);

           
            dap.SelectCommand.CommandText = sql.ToString();
            dap.Fill(ds,"sjzd");
            dataGridView1.DataSource = ds.Tables["sjzd"];
            ds.Tables[0].DefaultView.AllowNew = false;
            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                label4.Text = "表名：" + ds.Tables[0].Rows[0][0];
                dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[1].Visible = false;
                dataGridView1.Columns[4].Visible = false;
                dataGridView1.Columns[5].Visible = false;
                dataGridView1.Columns[7].Visible = false;
                dataGridView1.Columns[9].Visible = false;

                dataGridView1.Columns[2].Width = 40;
                dataGridView1.Columns[8].Width = 40;

                textBox4.Enabled = true;
            }
            else
            {
                textBox4.Enabled = false;
            }

        }

        private void tabPage1_SizeChanged(object sender, EventArgs e)
        {
            asc1.controlAutoSize(this.tabPage1);
        }

        private void textBox4_TextChanged(object sender, EventArgs e)
        {
            ((DataTable)dataGridView1.DataSource).DefaultView.RowFilter = "列名称 like '%" + textBox4.Text.Trim() + "%'";
        }
       #endregion


        #region   sql整理
        private string oldsql = "";//原sql字符串
        private string Result = "";                  //结果字符串
        //List<string> sonQuotes = new List<string>();//单引号中内容
        //List<string> sonNotes = new List<string>();//--后中内容
        private List<string> sonResult;
        private List<int> order;//括号替换顺序
        private List<int> newLineIndex;//换行位置
        private void tabPage2_Enter(object sender, EventArgs e)
        {
            asc2.controllInitializeSize(this.tabPage2);
        }
        private void tabPage2_SizeChanged(object sender, EventArgs e)
        {
            asc2.controlAutoSize(this.tabPage2);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Result = "";
            sonResult = new List<string>();
            order = new List<int>();//括号替换顺序
            newLineIndex = new List<int>();//换行位置
            oldsql = textBox2.Text.Trim();
            //ReplaceQuotes();
            //ReplaceNotes();
            Result = RemoveBlank(oldsql);

            //int doCount = 0;
            //while (Result.Contains("(") && Result.Contains(")"))//如果sql中含有“（）”
            //{
            //    //括号处理
            List<int> left = new List<int>();      //“（”左括号位置信息列表
            List<int> right = new List<int>();     //“）”右括号位置信息列表

            char[] char_Array = Result.ToCharArray();
            for (int i = 0; i < char_Array.Length; i++)
            {
                if (char_Array[i].ToString() == "(")//记录左括号位置
                {
                    left.Add(i);
                }
                else if (char_Array[i].ToString() == ")")//记录右括号位置
                {
                    right.Add(i);
                }
                else
                {
                    continue;
                }
            }
            if (left.Count == right.Count) //左右括号数量匹配则进行格式化
            {
                List<List<int>> D2location1 = new List<List<int>>();//一对括号位置(记录原始位置)
                List<List<int>> D2location2 = new List<List<int>>();//一对括号位置（计算使用）
                for (int i = 0; i < left.Count; i++)
                {
                    int countL = 0;
                    int countR = 0;
                    for (int j = left[i]; j < char_Array.Length; j++)
                    {
                        if (char_Array[j].ToString() == "(")
                        {
                            countL = countL + 1;
                        }
                        else if (char_Array[j].ToString() == ")")
                        {
                            countR = countR + 1;
                        }
                        if (countL != 0 && countL == countR)
                        {
                            List<int> L1 = new List<int>();
                            L1.Add(left[i]);
                            L1.Add(j);
                            D2location1.Add(L1);

                            List<int> L2 = new List<int>();
                            L2.Add(left[i]);
                            L2.Add(j);
                            D2location2.Add(L2);

                            break;
                        }
                    }

                }
                //构造字符串替代括号内容
                for (int i = 0; i < left.Count; i++)
                {
                    int index = GetMin(D2location2);
                    int indexL = D2location2[index][0];
                    int indexR = D2location2[index][1];
                    int len = indexR - indexL + 1;
                    sonResult.Add(Result.Substring(D2location2[index][0], len));
                    string s = "";
                    //构造相同长度字符串
                    for (int j = 0; j < len; j++)
                    {
                        s = s + "？";
                    }
                    Result = Result.Substring(0, indexL) + s + Result.Substring(indexR + 1, Result.Length - indexR - 1);
                    D2location2[index][0] = -1;
                    D2location2[index][1] = 100000;
                    order.Add(index);
                }
                /*格式化*/
                Result = doFormat(Result, 0);
                //反向装配
                for (int i = order.Count - 1; i >= 0; i--)
                {
                    int indexL = D2location1[order[i]][0];
                    int indexR = D2location1[order[i]][1];
                    Result = Result.Substring(0, indexL) + doFormat(sonResult[i], indexL) + Result.Substring(indexR + 1, Result.Length - indexR - 1);
                }
                doNewLine();
                textBox3.Text = Result;

            }
            else//否则结束
            {
                textBox2.Text = "左括号与右括号数量不匹配！请确保输入sql正确，再进行格式化！";
                return;
            }  
        }

        /*获得最内层括号下标*/
        private int GetMin(List<List<int>> location)
        {
            int min = 100000;
            int index = -1;
            for (int i = 0; i < location.Count; i++)
            {
                if (location[i][1] - location[i][0] < min)
                {
                    min = location[i][1] - location[i][0];
                    index = i;
                }
            }
            return index;
        }

        //自定义函数
        private string RemoveBlank(string oldstr)  //去掉多余空格：去掉前后空格，中间多个空格变成一个空格
        {
            string[] str = oldstr.Split();
            string newstr = "";
            for (int i = 0; i < str.Length; i++)
            {
                if (str[i] != "")
                {
                    if (i != str.Length - 1)
                    {
                        newstr += str[i].ToString().Trim() + " ";
                    }
                    else
                    {
                        newstr += str[i].ToString().Trim();
                    }
                }
            }
            return newstr;
        }

        private string doFormat(string strsql, int L)
        {
            List<int> index = new List<int>();
            index.Add(strsql.ToLower().IndexOf("select"));
            index.Add(strsql.ToLower().IndexOf("from"));
            index.Add(strsql.ToLower().IndexOf("where"));
            index.Add(strsql.ToLower().IndexOf("group by"));
            index.Add(strsql.ToLower().IndexOf("having"));
            index.Add(strsql.ToLower().IndexOf("order by"));
            List<List<int>> se = new List<List<int>>();
            for (int i = 0; i < 6; i++)//确定关键字区间
            {
                List<int> a = new List<int>();
                if (index[i] != -1)
                {
                    a.Add(index[i]);
                    newLineIndex.Add(index[i] + L);
                    for (int j = i + 1; j < 6; j++)
                    {
                        if (index[j] != -1)
                        {
                            a.Add(index[j]);
                            break;
                        }
                        else if (j < 5)
                        {
                            continue;
                        }
                        else
                        {
                            a.Add(strsql.Length - 1);
                        }
                    }
                    if (i == 5)
                    {
                        a.Add(strsql.Length - 1);
                    }
                }
                else
                {
                    a.Add(-1);
                    a.Add(-1);
                }
                se.Add(a);

            }
            //格式化关键字区间
            string substr = "";
            if (se[0][0] != -1)//select
            {
                substr = strsql.Substring(se[0][0], se[0][1] - se[0][0]);
                char[] char_Selct = substr.ToCharArray();
                for (int i = 0; i < char_Selct.Length; i++)
                {
                    if (char_Selct[i].ToString() == ",")
                    {
                        newLineIndex.Add(se[0][0] + i + L + 1);
                    }
                }
            }
            if (se[1][0] != -1)//from
            {
                substr = strsql.Substring(se[1][0], se[1][1] - se[1][0]);
                char[] char_From = substr.ToCharArray();
                for (int i = 0; i < char_From.Length; i++)
                {
                    if (i < char_From.Length - 8 && (char_From[i].ToString() + char_From[i + 1].ToString() + char_From[i + 2].ToString() + char_From[i + 3].ToString() + char_From[i + 4].ToString() + char_From[i + 5].ToString() + char_From[i + 6].ToString() + char_From[i + 7].ToString() + char_From[i + 8].ToString()).ToLower() == "left join")
                    {
                        newLineIndex.Add(se[1][0] + i + L);
                        i = i + 8;
                    }
                    else if (i < char_From.Length - 9 && (char_From[i].ToString() + char_From[i + 1].ToString() + char_From[i + 2].ToString() + char_From[i + 3].ToString() + char_From[i + 4].ToString() + char_From[i + 5].ToString() + char_From[i + 6].ToString() + char_From[i + 7].ToString() + char_From[i + 8].ToString() + char_From[i + 9].ToString()).ToLower() == "right join")
                    {
                        newLineIndex.Add(se[1][0] + i + L);
                        i = i + 9;
                    }
                    else if (i < char_From.Length - 9 && (char_From[i].ToString() + char_From[i + 1].ToString() + char_From[i + 2].ToString() + char_From[i + 3].ToString() + char_From[i + 4].ToString() + char_From[i + 5].ToString() + char_From[i + 6].ToString() + char_From[i + 7].ToString() + char_From[i + 8].ToString() + char_From[i + 9].ToString()).ToLower() == "inner join")
                    {
                        newLineIndex.Add(se[1][0] + i + L);
                        i = i + 9;
                    }
                    else if (i < char_From.Length - 8 && (char_From[i].ToString() + char_From[i + 1].ToString() + char_From[i + 2].ToString() + char_From[i + 3].ToString() + char_From[i + 4].ToString() + char_From[i + 5].ToString() + char_From[i + 6].ToString() + char_From[i + 7].ToString() + char_From[i + 8].ToString()).ToLower() == "full join")
                    {
                        newLineIndex.Add(se[1][0] + i + L);
                        i = i + 8;
                    }
                    else if (i < char_From.Length - 3 && (char_From[i].ToString() + char_From[i + 1].ToString() + char_From[i + 2].ToString() + char_From[i + 3].ToString()).ToLower() == "join")
                    {
                        newLineIndex.Add(se[1][0] + i + L);
                        i = i + 3;
                    }
                }
            }
            if (se[2][0] != -1)//where
            {
            }
            if (se[3][0] != -1)//group by
            {
                substr = strsql.Substring(se[3][0], se[3][1] - se[3][0]);
                char[] char_Group = substr.ToCharArray();
                for (int i = 0; i < char_Group.Length; i++)
                {
                    if (char_Group[i].ToString() == ",")
                    {
                        newLineIndex.Add(se[3][0] + i + L + 1);
                    }
                }
            }
            if (se[4][0] != -1)//having
            {
            }
            if (se[5][0] != -1)//order by
            {
                substr = strsql.Substring(se[5][0], se[5][1] - se[5][0]);
                char[] char_Order = substr.ToCharArray();
                for (int i = 0; i < char_Order.Length; i++)
                {
                    if (char_Order[i].ToString() == ",")
                    {
                        newLineIndex.Add(se[5][0] + i + L + 1);
                    }
                }
            }
            return strsql;
        }

        private void doNewLine()
        {
            newLineIndex.Sort();
            for (int i = newLineIndex.Count - 1; i > 0; i--)
            {
                Result = Result.Insert(newLineIndex[i], Environment.NewLine);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            textBox3.SelectAll();
            textBox3.Copy();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            textBox2.Clear();
        }
        #endregion

       


        #region   数据比对

        private void tabPage3_Enter(object sender, EventArgs e)
        {
            asc3.controllInitializeSize(this.tabPage3);
            string sqlConStr = sqlconstr;
            con = new SqlConnection(sqlConStr);
            dap = new SqlDataAdapter("", this.con);
            ds = new DataSet();
        }
        private void button5_Click(object sender, EventArgs e)
        {
            if (textBox6.Text.Trim() != "")
            {
            for (int i = 0; i < dataGridView2.Columns.Count; i++)
            {
                dataGridView2.Columns[i].Visible = true;
            }
            if (dataGridView2.DataSource != null)
                ds.Tables["sjbd"].Clear();
                string sql = "select * from " + textBox5.Text.Trim() + " where ID in(" + textBox6.Text.Trim() + "," + textBox7.Text.Trim() + ")";
                dap.SelectCommand.CommandText = sql;
                try
                {
                    dap.Fill(ds, "sjbd");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                dataGridView2.DataSource = ds.Tables["sjbd"];
                for (int i = 0; i < dataGridView2.Columns.Count; i++)
                {
                    if (dataGridView2.Rows.Count == 2 && dataGridView2.Rows[0].Cells[i].Value.Equals(dataGridView2.Rows[1].Cells[i].Value))
                    {
                        dataGridView2.Columns[i].Visible = false;
                    }
                }
            }
        }

        private void tabPage3_SizeChanged(object sender, EventArgs e)
        {
            asc3.controlAutoSize(this.tabPage3);
        }
        #endregion

       





    }
}
